
---buoc 1: keo bang limit_org ve may
DECLARE @DATE NVARCHAR(8)= (SELECT CONVERT(NVARCHAR(8),MAX( BUSINESS_DATE), 112) FROM SERVER74.STAGING.[DBO].[DLYTB_LIMIT_ORG] )

--SELECT BUSINESS_DATE, LIABILITY_NUMBER, ID,  APPROVAL_DATE, EXPIRY_DATE, ONLINE_LIMIT_DATE,
--PRODUCT_ALLOWED, INTERNAL_AMOUNT, FIXED_VARIABLE, COLLATERAL_CODE, MAXIMUM_SECURED, 
--MAXIMUM_UNSECURED, MAXIMUM_TOTAL, COLLAT_RIGHT, SECURED_AMT, ONLINE_LIMIT,TOTAL_OS, AVAIL_AMT,
--CREDIT_LINE, LIMIT_PRODUCT, CO_CODE, DEPT_CODE, LIMIT_CURRENCY
--INTO #A
--FROM SERVER74.STAGING.[DBO].[DLYTB_LIMIT_ORG] 
--WHERE  (ID LIKE '%.005%' OR ID LIKE '%.006%')
--and LIABILITY_NUMBER IN (SELECT CIF FROM TBL_CUSTOMER)

--drop table LIMIT_ORG_QD

SELECT A.*
,B.MID_RATE
,B.MID_RATE* 
CONVERT(FLOAT,(CASE WHEN INTERNAL_AMOUNT LIKE '%B%'THEN REPLACE(INTERNAL_AMOUNT,'B','000000000')
WHEN INTERNAL_AMOUNT LIKE'%M%'THEN REPLACE(INTERNAL_AMOUNT,'M','000000') 
ELSE INTERNAL_AMOUNT END)) AS INTENRNAL_AMOUNT_QD,
B.MID_RATE* 
CONVERT(FLOAT, (CASE WHEN ONLINE_LIMIT LIKE '%B%'THEN REPLACE(ONLINE_LIMIT,'B','000000000')
WHEN ONLINE_LIMIT LIKE'%M%'THEN REPLACE(ONLINE_LIMIT,'M','000000') 
ELSE ONLINE_LIMIT END)) AS ONLINE_LIMIT_QD,
B.MID_RATE* 
CONVERT(FLOAT, (CASE WHEN TOTAL_OS LIKE'%B%'THEN REPLACE(TOTAL_OS,'B','000000000')
WHEN TOTAL_OS LIKE'%M%'THEN REPLACE(TOTAL_OS,'M','000000') 
ELSE TOTAL_OS END)) AS TOTAL_OS_QD
,B.MID_RATE* 
CONVERT(FLOAT,(CASE WHEN AVAIL_AMT LIKE'%B%'THEN REPLACE(AVAIL_AMT,'B','000000000')
WHEN AVAIL_AMT LIKE'%M%'THEN REPLACE(AVAIL_AMT,'M','000000') 
ELSE AVAIL_AMT END)) AS AVAIL_AMT_QD
            ,B.MID_RATE* 
CONVERT(FLOAT,(CASE WHEN MAXIMUM_UNSECURED LIKE'%B%'THEN REPLACE(MAXIMUM_UNSECURED,'B','000000000')
WHEN MAXIMUM_UNSECURED LIKE'%M%'THEN REPLACE(MAXIMUM_UNSECURED,'M','000000') 
ELSE MAXIMUM_UNSECURED END) ) AS MAXIMUM_UNSECURED_QD
            INTO LIMIT_ORG_QD
FROM #A A
LEFT JOIN [SERVER16].[VPB_WHR2].DBO.FOCURR_SAVE B
ON A.LIMIT_CURRENCY=B.CODE
WHERE SAVE_DATE = @date

select * from LIMIT_ORG_QD
